package DatabaseManager;

import java.sql.*;

public class UserDBManager {
	
	private Connection con;
	private PreparedStatement preparedStmt;
	private ResultSet resultSet;
	
	private String url;
	private String id;
	private String pw;
	
	public UserDBManager(){
		con = null;
		preparedStmt = null;
		resultSet = null;
		url = "jdbc:mysql://127.0.0.1:3306/PDUMSGBox"; //jdbc:mysql://url:port/'DBname'
		id = "root";
		pw = "wlqwndrydbr";
	}
	
	public void ConnectionDB(){
		try {
			Class.forName("com.mysql.jdbc.Driver"); // JDBC 占쏙옙占쏙옙譴占쏙옙占�클占쏙옙占쏙옙 占싱몌옙
													// 占쏙옙占쏙옙占쏙옙 占쏙옙占쏙옙 占쏙옙占쏙옙占싶븝옙占싱쏙옙占쏙옙 URI占쏙옙 占쏙옙占쏙옙 - DB占쏙옙:test
													// 占쌨소듸옙 호占쏙옙 ("DB占쏙옙 URI", "DB 占쏙옙占싱듸옙", "DB 占쏙옙占�)
			con = DriverManager.getConnection(url,id,pw);
			System.out.println("DB 占쏙옙占쏙옙 확占쏙옙");
			//conn.close(); // 占쏙옙占쏙옙占쏙옙 占쏙옙占쏙옙 占쌨소듸옙
		}
		catch (ClassNotFoundException cnte) {
			System.out.println(cnte.getMessage() );
		}
		catch (SQLException se) {
			System.out.println(se.getMessage());
		}
	}
	
	public void CloseConnectionDB() throws SQLException{
		con.close();
	}
	
	/**
	 * User占쏙옙 IP 占쌍쇽옙 占쏙옙占쏙옙
	 * @param phoneNumber
	 * @return
	 * @throws SQLException
	 */
	public String GetUserAddress(String userId) throws SQLException
	{
		String userAddress = new String();
		String sql = "select UserAddress from UserDB where UserID = ?";
		preparedStmt = con.prepareStatement(sql);
		preparedStmt.setString(1, userId);
		resultSet = preparedStmt.executeQuery();
		
		userAddress = resultSet.getString("UserAddress");
		
		return userAddress; 
	}

	public String GetUserID(String userAddress) throws SQLException
	{
		String userId = new String();
		String sql = "select UserID from UserDB where UserAddress = ?";
		preparedStmt = con.prepareStatement(sql);
		preparedStmt.setString(1, userAddress);
		resultSet = preparedStmt.executeQuery();
		
		userAddress = resultSet.getString("UserID");
		
		return userId; 
	}
	/**
	 * User占쏙옙 占쏙옙占쏙옙占쏙옙 (On/Off)占쏙옙占쏙옙
	 * @param phoneNumber
	 * @return
	 * @throws SQLException
	 */
	public String GetUserState(String phoneNumber) throws SQLException
	{
		String userState = new String();
		String sql = "select UserState from UserDB where PhoneNumber = ?";
		preparedStmt = con.prepareStatement(sql);
		preparedStmt.setString(1, phoneNumber);
		resultSet = preparedStmt.executeQuery();
		
		userState = resultSet.getString("UserState");
		
		return userState; 
	}
	
	/**
	 * User占쏙옙 占쏙옙占�占쏙옙占쏙옙 占쏙옙占쏙옙
	 * @param phoneNumber
	 * @param userState
	 * @throws SQLException
	 */
	public void UpdateUserState(String phoneNumber, String userState)throws SQLException
	{
		String sql = "update UserDB set UserState = ? where PhoneNumber = ?";
		preparedStmt = con.prepareStatement(sql);
		preparedStmt.setString(1, userState);
		preparedStmt.setString(2, phoneNumber);
		preparedStmt.executeUpdate();
	}
	public void UpdateUserAddress(String userId, String userAddress)throws SQLException
	{
		String sql = "select UserID from UserDB where UserID = ?";
		preparedStmt = con.prepareStatement(sql);
		preparedStmt.setString(1, userId);
		resultSet = preparedStmt.executeQuery();
		if(resultSet.next()){
			sql = "update UserDB set UserAddress = ? where UserID = ?";
			preparedStmt = con.prepareStatement(sql);
			preparedStmt.setString(1, userAddress);
			preparedStmt.setString(2, userId);
			preparedStmt.executeUpdate();
		}else{
			sql = "insert into UserDB values(?,?,?,?,?,?)";
			preparedStmt = con.prepareStatement(sql);
			preparedStmt.setString(1, userId);
			preparedStmt.setString(2, userAddress);
			preparedStmt.setString(3, null);
			preparedStmt.setString(4, null);
			preparedStmt.setString(5, null);
			preparedStmt.setString(6, null);
			preparedStmt.executeUpdate();
		}		
	}
}
